package com.iflytek.service;

import com.iflytek.util.GetFileName;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.stereotype.Service;

import java.io.InputStream;
import java.io.FileOutputStream;
import java.util.HashMap;
import java.util.Map;

@Service
public class RequestAbilityService {

    private static final String INPUT_DIR = "src/main/resources/input/RequestAbility";

    public void processExcelFiles(String outputFilePath) {
        try {
            // 获取输入文件名字
            String requestAbilityFile = GetFileName.getSingleFileName(INPUT_DIR);
            // 读取 resources 目录下的 映射 文件
            InputStream file2Stream = getClass().getClassLoader().getResourceAsStream("应用标识-业务线的映射.xlsx");
            InputStream file3Stream = getClass().getClassLoader().getResourceAsStream("input/RequestAbility/"+requestAbilityFile);

            if (file2Stream == null || file3Stream == null) {
                throw new RuntimeException("资源文件不存在！");
            }

            Workbook workbook2 = new XSSFWorkbook(file2Stream);
            Workbook workbook3 = new XSSFWorkbook(file3Stream);
            Workbook outputWorkbook = new XSSFWorkbook();

            // 读取文件2的应用标识到业务线的映射
            Sheet sheet2 = workbook2.getSheetAt(0);
            Map<String, String> mapping = new HashMap<>();
            for (int i = 1; i <= sheet2.getLastRowNum(); i++) {
                Row row = sheet2.getRow(i);
                if (row == null) continue;
                String appId = row.getCell(0).getStringCellValue().trim(); // 应用标识
                String businessLine = row.getCell(1).getStringCellValue().trim(); // 业务线
                mapping.put(appId, businessLine);
            }

            // 读取文件3，处理应用标识、任务类型、请求类型过滤和统计
            Sheet sheet3 = workbook3.getSheetAt(0);
            Map<String, Map<String, Double>> groupedData = new HashMap<>();

            Row headerRow = sheet3.getRow(0); // 获取文件3的表头
            Map<String, Integer> columnIndexMap = new HashMap<>();

            // 获取列索引
            for (int i = 0; i < headerRow.getLastCellNum(); i++) {
                Cell cell = headerRow.getCell(i);
                if (cell != null) {
                    columnIndexMap.put(cell.getStringCellValue().trim(), i);
                }
            }

            Integer appIdIndex = columnIndexMap.get("应用标识");
            Integer taskTypeIndex = columnIndexMap.get("任务类型");
            Integer requestTypeIndex = columnIndexMap.get("请求类型同异步");
            Integer resultIndex = columnIndexMap.get("统计结果");

            if (appIdIndex == null || taskTypeIndex == null || requestTypeIndex == null || resultIndex == null) {
                throw new RuntimeException("未找到必要的列，请检查表头是否包含 '应用标识'、'任务类型'、'请求类型同异步' 和 '统计结果'");
            }

            // 按行遍历文件3的数据
            for (int i = 1; i <= sheet3.getLastRowNum(); i++) {
                Row row = sheet3.getRow(i);
                if (row == null) continue;

                // 获取应用标识、任务类型、请求类型同异步、统计结果
                Cell appIdCell = row.getCell(appIdIndex);
                Cell taskTypeCell = row.getCell(taskTypeIndex);
                Cell requestTypeCell = row.getCell(requestTypeIndex);
                Cell resultCell = row.getCell(resultIndex);

                // 忽略请求类型为“内部”的行
                if (requestTypeCell != null && "内部".equals(requestTypeCell.getStringCellValue().trim())) {
                    continue;
                }

                String appId = (appIdCell != null) ? appIdCell.getStringCellValue().trim() : "";
                String taskType = (taskTypeCell != null) ? taskTypeCell.getStringCellValue().trim() : "";
                String businessLine = mapping.getOrDefault(appId, "未知业务线");

                double result = 0.0;
                if (resultCell != null) {
                    switch (resultCell.getCellType()) {
                        case NUMERIC:
                            result = resultCell.getNumericCellValue();
                            break;
                        case STRING:
                            try {
                                result = Double.parseDouble(resultCell.getStringCellValue().trim());
                            } catch (NumberFormatException e) {
                                result = 0.0; // 无法解析时设为默认值
                            }
                            break;
                        default:
                            result = 0.0;
                    }
                }

                // 累加统计结果
                groupedData.putIfAbsent(taskType, new HashMap<>());
                Map<String, Double> taskData = groupedData.get(taskType);
                taskData.put(businessLine, taskData.getOrDefault(businessLine, 0.0) + result);
            }

            // 新建输出 Sheet
            Sheet outputSheet = outputWorkbook.createSheet("Result");

            // 写入表头
            Row headerRow1 = outputSheet.createRow(0);
            headerRow1.createCell(0).setCellValue("任务类型");
            headerRow1.createCell(1).setCellValue("业务线");
            headerRow1.createCell(2).setCellValue("统计结果");

            // 写入数据
            int rowIndex = 1;
            for (Map.Entry<String, Map<String, Double>> entry : groupedData.entrySet()) {
                String taskType = entry.getKey();
                Map<String, Double> taskData = entry.getValue();

                for (Map.Entry<String, Double> taskEntry : taskData.entrySet()) {
                    String businessLine = taskEntry.getKey();
                    double result = taskEntry.getValue();

                    Row newRow = outputSheet.createRow(rowIndex++);
                    newRow.createCell(0).setCellValue(taskType);
                    newRow.createCell(1).setCellValue(businessLine);
                    newRow.createCell(2).setCellValue(result);
                }
            }

            // 保存结果到输出文件
            try (FileOutputStream fos = new FileOutputStream(outputFilePath)) {
                outputWorkbook.write(fos);
            }

            System.out.println("文件处理完成，结果保存到：" + outputFilePath);

        } catch (Exception e) {
            e.printStackTrace();
        }
    }
}
